﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

namespace SBT_Tools
{
    public enum Status
    {
        Fail,
        Succees,
    }

    /// <summary>
    ///  .SBT标签文件对象。SBT:Symbol Base Tags
    /// </summary>
    class SBT
    {
        private SQLiteConnection conn;  //数据库连接对象
        private SQLiteCommand cmd;  //sql命令

        public string File_fullPath;

        public SBT() { }
        public SBT(string file_fullPath)
        {
            Create_And_Open(file_fullPath);
        }

        public Status Create_And_Open(string file_fullPath)
        {            
            Open(file_fullPath);
            Create_Tables();
            return Status.Succees;
        }

        public Status Open(string file_fullPath)
        {
            File_fullPath = file_fullPath;
            if (conn != null)
                conn.Close();
            conn = new SQLiteConnection("Data Source=" + file_fullPath);
            conn.Open();
            if (cmd == null)
                cmd = conn.CreateCommand();
            return Status.Succees;
        }
        public Status Close()
        {
            if (conn != null)
                conn.Close();
            return Status.Succees;
        }
        /// <summary>
        /// 创建表结构
        /// </summary>
        /// <returns></returns>
        private Status Create_Tables()
        {
            cmd.CommandText = "CREATE TABLE if not exists \"Target\"(\"ID\"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\"Name\"  TEXT,\"Comment\"  TEXT);";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "CREATE TABLE if not exists \"Tag\" (\"ID\"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\"Name\"  TEXT,\"Comment\"  TEXT,\"Refer\"  INTEGER); ";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "CREATE TABLE if not exists \"Map_Target_Tag\" (\"ID\"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\"TargetID\"  INTEGER,\"TagID\"  INTEGER); ";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "CREATE TABLE if not exists \"Map_Tag_Tag\" (ID  INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL ,ParentID INTEGER,ChildID INTEGER);";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "CREATE VIEW if not exists overview AS select a.Name,group_concat(b.Name,\"；\") from Target a,Map_Target_Tag m,Tag b where a.ID =m.TargetID and m.TagId =b.ID group by m.TargetID; ";
            cmd.ExecuteNonQuery();
            return Status.Succees;
        }

        public Status Add_Two_String(string table, string name = "", string comment = "")
        {
            if (table != Name.Target && table != Name.Tag)
                return Status.Fail;

            cmd.CommandText = "insert or ignore into " + table + "(Name,Comment) values (@name,@comment);";
            cmd.Parameters.Add(new SQLiteParameter("@name", name));
            cmd.Parameters.Add(new SQLiteParameter("@comment", comment));
            cmd.ExecuteNonQuery();

            return Status.Succees;
        }

        public Status Add_Two_Int(string table, int param1, int param2)
        {
            if (table != Name.Map_Target_Tag && table != Name.Map_Tag_Tag)
                return Status.Fail;

            if (table == Name.Map_Target_Tag)
            {
                cmd.CommandText = string.Format("INSERT OR IGNORE INTO Map_Target_Tag(TargetID,TagID) VALUES ({0},{1})",param1,param2);
                cmd.ExecuteNonQuery();
            }else if(table==Name.Map_Tag_Tag)
            {
                cmd.CommandText = string.Format("INSERT OR IGNORE INTO Map_Tag_Tag (ParentID,ChildID) VALUES ({0},{1})",param1,param2);
                cmd.ExecuteNonQuery();
            }

            return Status.Succees;
        }

        public int Get_Info_ID(string table,string Name)
        {

            return -1;
        }

        public static class Name
        {
            public  const string Target = "Target";
            public  const string Tag = "Tag";
            public  const string Map_Target_Tag = "Map_Target_Tag";
            public  const string Map_Tag_Tag = "Map_Tag_Tag";
        }
        

    }

}
